-- 创建竞赛表
CREATE TABLE IF NOT EXISTS competitions (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(50) DEFAULT 'hackathon',
    platform VARCHAR(100) NOT NULL,
    prize_pool VARCHAR(50),
    prize_currency VARCHAR(10) DEFAULT 'USD',
    deadline TIMESTAMP,
    participants_count INTEGER DEFAULT 0,
    max_participants INTEGER,
    difficulty VARCHAR(20) DEFAULT 'intermediate',
    category VARCHAR(50),
    tags TEXT[], -- PostgreSQL数组类型
    banner_image TEXT,
    external_url TEXT,
    status VARCHAR(20) DEFAULT 'ongoing',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建更新时间触发器
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_competitions_updated_at
    BEFORE UPDATE ON competitions
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 启用行级安全策略
ALTER TABLE competitions ENABLE ROW LEVEL SECURITY;

-- 创建策略：允许所有人读取
CREATE POLICY "Allow public read access" ON competitions
    FOR SELECT USING (true);

-- 创建策略：允许认证用户创建
CREATE POLICY "Allow authenticated users to create" ON competitions
    FOR INSERT WITH CHECK (auth.role() = 'authenticated');

-- 创建策略：允许认证用户更新
CREATE POLICY "Allow authenticated users to update" ON competitions
    FOR UPDATE USING (auth.role() = 'authenticated');

-- 授权给anon和authenticated角色
GRANT SELECT ON competitions TO anon;
GRANT ALL PRIVILEGES ON competitions TO authenticated;
GRANT USAGE, SELECT ON SEQUENCE competitions_id_seq TO authenticated;